﻿using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using CompanyDB;
using System.Data.SqlClient;
using System.Data.Objects;
using System;
using System.Linq;
using System.IO;
using System.Collections.Generic;

namespace CompanyDALTest
{


    /// <summary>
    ///This is a test class for CompanyDALTest and is intended
    ///to contain all CompanyDALTest Unit Tests
    ///</summary>
    [TestClass()]
    public class CompanyDALTest
    {
        public const string PRODUCTION_DB_NAME = "CompanyDatabaseNew";
        public const string TEST_DB_NAME = "CompanyDatabaseNewTest";
        public const string CLONE_CONNECTION_STRING =
            @"Data Source=System;Initial Catalog=master;Integrated Security=True;MultipleActiveResultSets=True";

        private static string databaseDirectory;

        #region Additional test attributes

        [ClassInitialize()]
        public static void InitTestDatabase(TestContext testContext)
        {
            databaseDirectory = testContext.TestDeploymentDir;

            CloneDb();

            //PopulateTestingData();
        }

        private static void CloneDb()
        {
            // Clone the production database over the testing database
            string sqlCloneProductionDB = String.Format(@"
				USE master

				BACKUP DATABASE {0} TO DISK='C:\Temp\{0}.bak'

				IF EXISTS(SELECT * FROM sys.sysdatabases where name='{2}')
				BEGIN
					ALTER DATABASE {2} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
					DROP DATABASE {2}
				END

				RESTORE DATABASE {2} FROM DISK='C:\Temp\{0}.bak'
				WITH
				MOVE '{0}' TO 'C:\Temp\{2}.mdf',
				MOVE '{0}_log' TO 'C:\Temp\{2}_Log.ldf'
			", PRODUCTION_DB_NAME, databaseDirectory, TEST_DB_NAME);

            SqlConnection dbConnection =
                new SqlConnection(CLONE_CONNECTION_STRING);
            dbConnection.Open();
            using (dbConnection)
            {
                SqlCommand cloneDBCmd = new SqlCommand(
                    sqlCloneProductionDB, dbConnection);
                cloneDBCmd.ExecuteNonQuery();
            }
        }

        [ClassCleanup()]
        public static void MyClassCleanup()
        {
            // Drop the testing database
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            string sqlDropTestDatabase = String.Format(@"
        				USE master
        
        				IF EXISTS(SELECT * FROM sys.sysdatabases where name='{0}')
        				BEGIN
        					ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
        					DROP DATABASE {0}
        				END
        			", TEST_DB_NAME);
            dbContext.ExecuteStoreCommand(sqlDropTestDatabase);
            File.Delete(@"C:\Temp\" + PRODUCTION_DB_NAME + ".bak");
        }


        /*
        private static void PopulateTestingData()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            using (dbContext)
            {
                try
                {
                    Country country = new Country()
                    {
                        Name = "new1",
                        NativeLanguage = "new1"
                    };
                    dbContext.Countries.AddObject(country);
                    dbContext.SaveChanges();

                    country = new Country()
                    {
                        Name = "new2",
                        NativeLanguage = "new2"
                    };
                    dbContext.Countries.AddObject(country);
                    dbContext.SaveChanges();

                    country = new Country()
                    {
                        Name = "new3",
                        NativeLanguage = "new3"
                    };
                    dbContext.Countries.AddObject(country);
                    dbContext.SaveChanges();

                    Company company = new Company()
                    {
                        Name = "Lemiriks",
                        CountryID = 1,
                        Industry = "Software"
                    };
                    dbContext.Companies.AddObject(company);
                    dbContext.SaveChanges();

                    company = new Company()
                    {
                        Name = "Lemiriks2",
                        CountryID = 2,
                        Industry = "Software"
                    };
                    dbContext.Companies.AddObject(company);
                    dbContext.SaveChanges();

                    company = new Company()
                    {
                        Name = "Lemiriks3",
                        CountryID = 3,
                        Industry = "Software"
                    };
                    dbContext.Companies.AddObject(company);
                    dbContext.SaveChanges();

                    Person person = new Person()
                    {
                        FirstName = "Pesho",
                        LastName = "Peshof",
                        Telephone = "0998",
                        CountryID = 1
                    };
                    dbContext.People.AddObject(person);
                    dbContext.SaveChanges();

                    person = new Person()
                    {
                        FirstName = "Pesho",
                        LastName = "Peshof",
                        Telephone = "0998",
                        CountryID = 2
                    };
                    dbContext.People.AddObject(person);
                    dbContext.SaveChanges();

                    person = new Person()
                    {
                        FirstName = "Pesho",
                        LastName = "Peshof",
                        Telephone = "0998",
                        CountryID = 3
                    };
                    dbContext.People.AddObject(person);
                    dbContext.SaveChanges();
                }
                catch (Exception)
                {
                }
            }
        }
        */
        //
        //Use ClassCleanup to run code after all tests in a class have run
        //       
        //
        //Use TestInitialize to run code before running each test
        //[TestInitialize()]
        //public void MyTestInitialize()
        //{
        //}
        //
        //Use TestCleanup to run code after each test has run
        //[TestCleanup()]
        //public void MyTestCleanup()
        //{
        //}
        //
        #endregion
        
        #region TestAdd
        /// <summary>
        ///A test for AddCompany
        ///</summary>
        [TestMethod()]
        public void AddCompanyTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            using (dbContext)
            {
                Country country = new Country()
                {
                    Name = "TestBulgaria2",
                    NativeLanguage = "TestBulgarian2"
                };
                CompanyDAL dal = new CompanyDAL();
                int countryID = dal.AddCountry(country);

                Company company = new Company()
                {
                    Name = "Lem1",
                    CountryID = countryID,
                    Industry = "Software"
                };
                int companyID = dal.AddCompany(company);
                Assert.IsTrue(companyID > 0);
            }
        }

        /// <summary>
        ///A test for AddCountry
        ///</summary>
        [TestMethod()]
        public void AddCountryTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            using (dbContext)
            {
                Country country = new Country()
                {
                    Name = "TestBulgaria3",
                    NativeLanguage = "TestBulgarian3"
                };
                CompanyDAL dal = new CompanyDAL();
                int countryID = dal.AddCountry(country);


                Assert.IsTrue(countryID != 0);
                Assert.IsTrue(countryID > 0);

            }
        }

        /// <summary>
        ///A test for AddEmployee
        ///</summary>
        [TestMethod()]
        public void AddEmployeeTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            using (dbContext)
            {
                Country countryObj = new Country()
                {
                    Name = "TestBulgaria4",
                    NativeLanguage = "TestBulgarian"
                };
                CompanyDAL dal = new CompanyDAL();
                int countryID = dal.AddCountry(countryObj);

                Person personObj = new Person()
                {
                    FirstName = "Pesho",
                    LastName = "Peshof",
                    Telephone = "0998",
                    CountryID = countryID
                };
                int resultPersonID = dal.AddPerson(personObj);

                Company companyObj = new Company()
                {
                    Name = "Lem2",
                    Industry = "Software",
                    CountryID = countryID
                };
                int resultCompanyID = dal.AddCompany(companyObj);

                Employee emplObj = new Employee()
                {
                    HireDate = DateTime.Now,
                    Salary = 10000,
                    PersonID = resultPersonID,
                    CompanyID = resultCompanyID
                };

                dal.AddEmployee(emplObj);

                string sqlQuery = "SELECT COUNT(*) FROM Employees WHERE PersonID={0} AND CompanyID={1}";

                object[] parameters = { resultPersonID, resultCompanyID };
                var queryResult = dbContext.ExecuteStoreQuery<int>(sqlQuery, parameters);
                int customersCount = queryResult.FirstOrDefault();

                Assert.IsTrue(customersCount == 1);
	        }
        }

        /// <summary>
        ///A test for AddPerson
        ///</summary>
        [TestMethod()]
        public void AddPersonTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            Random rand = new Random();
            using (dbContext)
            {
                int randValue = rand.Next(0, 10000);
                Country country = new Country()
                {
                    Name = "TestBulgaria" + randValue.ToString(),
                    NativeLanguage = "TestBulgarian" + randValue.ToString()
                };
                CompanyDAL dal = new CompanyDAL();
                int countryID = dal.AddCountry(country);

                Person obj = new Person()
                {
                    FirstName = "Pesho",
                    LastName = "Peshof",
                    Telephone = "0998",
                    CountryID = countryID
                };


                //CompanyDAL dal = new CompanyDAL();
                int resultObjID = dal.AddPerson(obj);

                Assert.IsTrue(resultObjID != 0);
                Assert.IsTrue(resultObjID > 0);

            }
        }

        /// <summary>
        ///A test for AddCustomer
        ///</summary>
        [TestMethod()]
        public void AddCustomerTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            using (dbContext)
            {
                Country countryObj = new Country()
                {
                    Name = "TestBulgaria5",
                    NativeLanguage = "TestBulgarian5"
                };
                CompanyDAL dal = new CompanyDAL();
                int countryID = dal.AddCountry(countryObj);

                Person personObj = new Person()
                {
                    FirstName = "Pesho",
                    LastName = "Peshof",
                    Telephone = "0998",
                    CountryID = countryID
                };
                int resultPersonID = dal.AddPerson(personObj);

                Company companyObj = new Company()
                {
                    Name = "Lem3",
                    Industry = "Software",
                    CountryID = countryID
                };
                int resultCompanyID = dal.AddCompany(companyObj);

                Customer customerObj = new Customer()
                {
                    CompanyID = resultCompanyID,
                    PersonID = resultPersonID
                };

                dal.AddCustomer(customerObj);

                string sqlQuery = "SELECT COUNT(*) FROM Customers WHERE PersonID={0} AND CompanyID={1}";

                object[] parameters = { resultPersonID, resultCompanyID };
                var queryResult = dbContext.ExecuteStoreQuery<int>(sqlQuery, parameters);
                int customersCount = queryResult.FirstOrDefault();

                Assert.IsTrue(customersCount == 1);
            }
        }
        #endregion
        
        /// <summary>
        ///A test for EditCustomer
        ///</summary>
        [TestMethod()]
        public void EditCustomerTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            using (dbContext)
            {
                Country countryObj = new Country()
                {
                    Name = "TestBulgaria6",
                    NativeLanguage = "TestBulgarian6"
                };
                CompanyDAL dal = new CompanyDAL();
                int countryID = dal.AddCountry(countryObj);

                Person personObj = new Person()
                {
                    FirstName = "Pesho",
                    LastName = "Peshof",
                    Telephone = "0998",
                    CountryID = countryID
                };
                int resultPersonID = dal.AddPerson(personObj);

                Company companyObj = new Company()
                {
                    Name = "Lem4",
                    Industry = "Software",
                    CountryID = countryID
                };
                int resultCompanyID = dal.AddCompany(companyObj);

                Customer customerObj = new Customer()
                {
                    CompanyID = resultCompanyID,
                    PersonID = resultPersonID
                };

                dal.AddCustomer(customerObj);

                companyObj = new Company()
                {
                    Name = "Lem5",
                    Industry = "Software",
                    CountryID = countryID
                };
                resultCompanyID = dal.AddCompany(companyObj);

                customerObj.CompanyID = resultCompanyID;

                dal.EditCustomer(customerObj);


                string sqlQuery = "SELECT CompanyID FROM Customers WHERE PersonID={0} AND CompanyID={1}";

                object[] parameters = { resultPersonID, resultCompanyID };
                var queryResult = dbContext.ExecuteStoreQuery<int>(sqlQuery, parameters);
                int customerCompanyID = queryResult.FirstOrDefault();

                Assert.IsTrue(customerCompanyID == resultCompanyID);
            }
        }

        /// <summary>
        ///A test for EditCustomer
        ///</summary>
        [TestMethod()]
        public void DeleteCustomerTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();

            Country countryObj = new Country()
            {
                Name = "TestBulgaria7",
                NativeLanguage = "TestBulgarian7"
            };
            CompanyDAL dal = new CompanyDAL();
            int countryID = dal.AddCountry(countryObj);

            Person personObj = new Person()
            {
                FirstName = "Pesho",
                LastName = "Peshof",
                Telephone = "0998",
                CountryID = countryID
            };
            int resultPersonID = dal.AddPerson(personObj);

            Company companyObj = new Company()
            {
                Name = "Lem5",
                Industry = "Software",
                CountryID = countryID
            };
            int resultCompanyID = dal.AddCompany(companyObj);

            Customer customerObj = new Customer()
            {
                CompanyID = resultCompanyID,
                PersonID = resultPersonID
            };

            dal.AddCustomer(customerObj);

            dal.DeleteCustomer(customerObj);

            IQueryable<Customer> res = from c in dbContext.Customers
                                       where c.CompanyID == resultCompanyID && c.PersonID == resultPersonID
                                       select c;

            Assert.IsTrue(!res.Any());
        }

        /// <summary>
        ///A test for AddEmployee
        ///</summary>
        [TestMethod()]
        public void TestStoredProcedure()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();
            using (dbContext)
            {
                Country countryObj = new Country()
                {
                    Name = "TestBulgaria_usp",
                    NativeLanguage = "TestBulgarian_usp"
                };
                CompanyDAL dal = new CompanyDAL();
                int countryID = dal.AddCountry(countryObj);

                Person personObj = new Person()
                {
                    FirstName = "Pesho_usp",
                    LastName = "Peshof_usp",
                    Telephone = "0998",
                    CountryID = countryID
                };
                int resultPersonID = dal.AddPerson(personObj);

                Company companyObj = new Company()
                {
                    Name = "Lem2_usp",
                    Industry = "Software_usp",
                    CountryID = countryID
                };
                int resultCompanyID = dal.AddCompany(companyObj);

                Employee emplObj = new Employee()
                {
                    HireDate = DateTime.Now,
                    Salary = 10000,
                    PersonID = resultPersonID,
                    CompanyID = resultCompanyID
                };

                dal.AddEmployee(emplObj);

                dal.RaiseSalary(companyObj, 100);

                Employee resultEmployee = dal.ReadEmployee(emplObj);

                Assert.IsTrue(resultEmployee.Salary == (decimal)10100);
            }
        }

        /// <summary>
        ///A test for ReadView
        ///</summary>
        [TestMethod()]
        public void ReadViewTest()
        {
            CompanyDatabaseNewEntities dbContext = new CompanyDatabaseNewEntities();

            Country countryObj = new Country()
            {
                Name = "TestBulgaria9",
                NativeLanguage = "TestBulgarian9"
            };
            CompanyDAL dal = new CompanyDAL();
            int countryID = dal.AddCountry(countryObj);

            Person personObj = new Person()
            {
                FirstName = "Pesho",
                LastName = "Peshof",
                Telephone = "0998",
                CountryID = countryID
            };
            int resultPersonID = dal.AddPerson(personObj);

            Company companyObj = new Company()
            {
                Name = "Lem9",
                Industry = "Software",
                CountryID = countryID
            };
            int resultCompanyID = dal.AddCompany(companyObj);

            Customer customerObj = new Customer()
            {
                CompanyID = resultCompanyID,
                PersonID = resultPersonID
            };

            dal.AddCustomer(customerObj);

            List<CompanyDAL.CustomersPerCompany> list = dal.ReadView();

            Assert.IsTrue(list.Count > 0);
        }
    }
}
